﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Configuration;
using com.hyrentals.data;
using com.hyrentals.common;
using ServiceStack.Text;

namespace com.hyrentals.dbaccess
{
    public class AccountDBAccess : DBAccessBase
    {
        public AccountDBAccess()
            : base(ConfigurationManager.ConnectionStrings["APPContext"].ConnectionString) { }

        public AccountDBAccess(string connectionString)
            : base(connectionString) { }

        public UserBasic GetUserBasicByEmail(string email)
        {
            UserBasic user = null;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_EMAIL, DBParamNameConstant.USER_EMAIL_DB_TYPE, DBParamNameConstant.USER_EMAIL_SIZE, email)
                                        };

                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[user_get_by_email]", parms);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dataRow = ds.Tables[0].Rows[0];

                    user = new UserBasic
                    {
                        UserId = (string)dataRow[DBColumnNameConstant.USER_ID],
                        Password = (string)dataRow[DBColumnNameConstant.USER_PASSWORD],
                        FirstName = (string)dataRow[DBColumnNameConstant.USER_FIRST_NAME],
                        LastName = (string)dataRow[DBColumnNameConstant.USER_LAST_NAME],
                        GenderType = (GenderTypeEnum)Enum.Parse(typeof(GenderTypeEnum), dataRow[DBColumnNameConstant.USER_GENDER].ToString()),
                        Email = (string)dataRow[DBColumnNameConstant.USER_EMAIL],
                        EmailStatus = (EmailStatusEnum)Enum.Parse(typeof(EmailStatusEnum), dataRow[DBColumnNameConstant.USER_EMAIL_STATUS].ToString()),
                        PhoneNumber = (dataRow[DBColumnNameConstant.USER_PHONE_NUMBER] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.USER_PHONE_NUMBER],
                        PhoneStatus = (dataRow[DBColumnNameConstant.USER_PHONE_STATUS] is DBNull) ? null : (PhoneStatusEnum?)Enum.Parse(typeof(PhoneStatusEnum), dataRow[DBColumnNameConstant.USER_PHONE_STATUS].ToString()),
                        AccountStatus = (AccountStatusEnum)Enum.Parse(typeof(AccountStatusEnum), dataRow[DBColumnNameConstant.USER_ACCOUNT_STATUS].ToString())
                    };
                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "GetUserBasicByEmail",
                    UserId = user.UserId,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return user;
        }

        public UserBasic GetUserBasicById(string userId)
        {
            UserBasic user = null;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_ID, DBParamNameConstant.USER_ID_DB_TYPE, DBParamNameConstant.USER_ID_SIZE, userId)
                                        };

                DataSet ds = DbHelper.ExecuteDataset(this._connectionString, CommandType.StoredProcedure, "[user_get_by_id]", parms);
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    DataRow dataRow = ds.Tables[0].Rows[0];

                    user = new UserBasic
                    {
                        UserId = (string)dataRow[DBColumnNameConstant.USER_ID],
                        Password = (string)dataRow[DBColumnNameConstant.USER_PASSWORD],
                        FirstName = (string)dataRow[DBColumnNameConstant.USER_FIRST_NAME],
                        LastName = (string)dataRow[DBColumnNameConstant.USER_LAST_NAME],
                        GenderType = (GenderTypeEnum)Enum.Parse(typeof(GenderTypeEnum), dataRow[DBColumnNameConstant.USER_GENDER].ToString()),
                        Email = (string)dataRow[DBColumnNameConstant.USER_EMAIL],
                        EmailStatus = (EmailStatusEnum)Enum.Parse(typeof(EmailStatusEnum), dataRow[DBColumnNameConstant.USER_EMAIL_STATUS].ToString()),
                        PhoneNumber = (dataRow[DBColumnNameConstant.USER_PHONE_NUMBER] is DBNull) ? null : (string)dataRow[DBColumnNameConstant.USER_PHONE_NUMBER],
                        PhoneStatus = (dataRow[DBColumnNameConstant.USER_PHONE_STATUS] is DBNull) ? null : (PhoneStatusEnum?)Enum.Parse(typeof(PhoneStatusEnum), dataRow[DBColumnNameConstant.USER_PHONE_STATUS].ToString()),
                        AccountStatus = (AccountStatusEnum)Enum.Parse(typeof(AccountStatusEnum), dataRow[DBColumnNameConstant.USER_ACCOUNT_STATUS].ToString())
                    };
                }
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "GetUserBasicById",
                    UserId = user.UserId,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }
            return user;
        }

        public bool InsertUser(string userId, string password, string firstName, string lastName,
            GenderTypeEnum genderType, string email, EmailStatusEnum emailStatus,
            AccountStatusEnum accountStatus, DateTime registrationDate)                                                                           
        {
            bool result = true;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_ID, DBParamNameConstant.USER_ID_DB_TYPE, DBParamNameConstant.USER_ID_SIZE, userId),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_PASSWORD, DBParamNameConstant.USER_PASSWORD_DB_TYPE, DBParamNameConstant.USER_PASSWORD_SIZE, password),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_FIRST_NAME, DBParamNameConstant.USER_FIRST_NAME_DB_TYPE, DBParamNameConstant.USER_FIRST_NAME_SIZE, firstName),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_LAST_NAME, DBParamNameConstant.USER_LAST_NAME_DB_TYPE, DBParamNameConstant.USER_LAST_NAME_SIZE, lastName),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_GENDER, DBParamNameConstant.USER_GENDER_DB_TYPE, DBParamNameConstant.USER_GENDER_SIZE, (int)genderType),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_EMAIL, DBParamNameConstant.USER_EMAIL_DB_TYPE, DBParamNameConstant.USER_EMAIL_SIZE, email),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_EMAIL_STATUS, DBParamNameConstant.USER_EMAIL_STATUS_DB_TYPE, DBParamNameConstant.USER_EMAIL_STATUS_SIZE, (int)emailStatus),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_ACOUNT_STATUS, DBParamNameConstant.USER_ACOUNT_STATUS_DB_TYPE, DBParamNameConstant.USER_ACOUNT_STATUS_SIZE, (int)accountStatus),
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_REGISTRATION_DATE, DBParamNameConstant.USER_REGISTRATION_DATE_DB_TYPE, DBParamNameConstant.USER_REGISTRATION_DATE_SIZE, registrationDate)
                                       };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[user_insert]", parms);
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "InsertUser",
                    UserId = userId,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());

                result = false;
            }

            return result;
        }

        public int GetUserCountByEmail(string email)
        {
            int count = 0;
            try
            {
                SqlParameter[] parms = {
                                            DbHelper.MakeInParam(DBParamNameConstant.USER_EMAIL, DBParamNameConstant.USER_EMAIL_DB_TYPE, DBParamNameConstant.USER_EMAIL_SIZE, email),
                                            DbHelper.MakeOutParam(DBParamNameConstant.COUNT, DBParamNameConstant.COUNT_DB_TYPE, DBParamNameConstant.COUNT_SIZE)
                                       };
                DbHelper.ExecuteNonQuery(this._connectionString, CommandType.StoredProcedure, "[user_get_count_by_email]", parms);
                count = (int)parms[1].Value;
            }
            catch (Exception ex)
            {
                Logger.Error(new
                {
                    Action = "GetUserCountByEmail",
                    Email = email,
                    Message = ex.Message,
                    StackTrace = ex.StackTrace
                }.ToJson());
            }

            return count;
        }

    }
}
